USE [vensoft]
GO
/****** Object:  StoredProcedure [dbo].[GetAllFromPersonal]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetAllFromPersonal]
AS
BEGIN
SELECT *
FROM Personal
WHERE Estado = 'V'
END
GO
/****** Object:  StoredProcedure [dbo].[GetFromClienteByNombre]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetFromClienteByNombre]
@nombre_razsocial varchar(30)
AS
BEGIN
SELECT *
FROM Cliente
WHERE upper(razon_social) = upper(@nombre_razsocial)
END
GO
/****** Object:  StoredProcedure [dbo].[GetFromPersonalByDni]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetFromPersonalByDni]
@DNI varchar(8)
AS
BEGIN
SELECT *
FROM Personal
WHERE upper(DNI) = upper(@DNI)
END
GO
/****** Object:  StoredProcedure [dbo].[RegistarCliente]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[RegistarCliente]
@codigoCliente int out,
@razon_social char(30),
@telefono char(9),
@direccion char(25),
@AP_Materno char(12),
@AP_Paterno char(12),
@Tipo char(18),
@DNI_RUC char(11),
@Estado char(1) = 'V'
as
begin
	declare 
	@cont int
	
	select @cont = COUNT(*) from Cliente where codigo_Cliente = @codigoCliente
	
	if @cont =0
		begin
			insert into Cliente(razon_social,telefono,direccion,AP_Materno,AP_Paterno,Tipo,DNI_RUC,Estado)
			             values(@razon_social,@telefono,@direccion,@AP_Materno,@AP_Paterno,@Tipo,@DNI_RUC,@Estado)
			             
			set @cont = SCOPE_IDENTITY()
		end
		
	if @cont = 1
		begin
			update Cliente 
				set razon_social = @razon_social,
				telefono = @telefono,
				direccion = @direccion,
				AP_Materno = @AP_Materno,
				AP_Paterno = @AP_Paterno,
				Tipo = @Tipo,
				DNI_RUC = @DNI_RUC,
				Estado = @Estado
			where codigo_Cliente = @codigoCliente		
			
		end
end
GO
/****** Object:  StoredProcedure [dbo].[RegistrarPersonal]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[RegistrarPersonal]
@DNI char(11),
@nombre char(30),
@telefono char(9),
@direccion char(25),
@AP_Materno char(12),
@AP_Paterno char(12),
@cargo char(12),
@Estado char(1) = 'V'
as
begin
	declare 
	@cont int
	
	select @cont = COUNT(*) from Personal where DNI = @DNI
	
	if @cont =0
		begin
			insert into Personal(DNI,Nombre,AP_Materno,AP_Paterno,Telefono,Direccion,Cargo,Estado)
			             values(@DNI,@nombre,@AP_Materno,@AP_Paterno,@telefono,@direccion,@cargo,@Estado)
			             
			set @cont = SCOPE_IDENTITY()
		end
		
	if @cont = 1
		begin		
			update Personal 
				set DNI = @DNI,
				nombre = @nombre,
				Telefono = @telefono,
				Direccion = @direccion,
				AP_Paterno = @AP_Paterno,
				AP_Materno = @AP_Materno,
				Cargo = @cargo,
				Estado = @Estado
			where DNI = @DNI		
			
		end
end
GO
/****** Object:  Table [dbo].[Cliente]    Script Date: 05/15/2011 23:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cliente](
	[codigo_Cliente] [int] IDENTITY(1,1) NOT NULL,
	[razon_social] [varchar](30) NOT NULL,
	[telefono] [varchar](9) NULL,
	[direccion] [varchar](25) NOT NULL,
	[AP_Materno] [varchar](12) NOT NULL,
	[AP_Paterno] [varchar](12) NOT NULL,
	[Tipo] [varchar](18) NOT NULL,
	[DNI_RUC] [varchar](11) NOT NULL,
	[Estado] [varchar](1) NOT NULL,
 CONSTRAINT [XPKCliente] PRIMARY KEY CLUSTERED 
(
	[codigo_Cliente] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Personal]    Script Date: 05/15/2011 23:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Personal](
	[DNI] [varchar](8) NOT NULL,
	[Nombre] [varchar](20) NOT NULL,
	[AP_Materno] [varchar](20) NOT NULL,
	[AP_Paterno] [varchar](20) NOT NULL,
	[Telefono] [varchar](9) NULL,
	[Direccion] [varchar](20) NOT NULL,
	[Cargo] [varchar](15) NOT NULL,
	[Estado] [varchar](1) NULL,
 CONSTRAINT [XPKPersonal] PRIMARY KEY CLUSTERED 
(
	[DNI] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Proveedor]    Script Date: 05/15/2011 23:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Proveedor](
	[Codigo_Proveedor] [int] IDENTITY(1,1) NOT NULL,
	[Razon_Social] [varchar](20) NOT NULL,
	[Telefono] [varchar](9) NOT NULL,
	[Descripcion] [varchar](40) NOT NULL,
	[RUC] [varchar](11) NOT NULL,
	[Estado] [varchar](1) NOT NULL,
 CONSTRAINT [PK_Proveedor] PRIMARY KEY CLUSTERED 
(
	[Codigo_Proveedor] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[Verifica]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Verifica]
@DNI varchar(18),
@passwor varchar(18)
AS
select count(*) from Usuario
where DNI=@DNI and passwor=@passwor
return
GO
/****** Object:  StoredProcedure [dbo].[CrearUsuario]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CrearUsuario]
@usuario nvarchar(18),
@passwor nvarchar(18)
AS

IF(SELECT DNI FROM  Personal  where DNI=@usuario)>0

INSERT INTO Usuario
(DNI,passwor) VALUES
(@usuario, @passwor)
GO
/****** Object:  StoredProcedure [dbo].[pa_buscarCliente]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pa_buscarCliente]
@DNI varchar(8 ),
@Apellido varchar (20)
AS
BEGIN
SELECT *
FROM Cliente
WHERE UPPER(DNI_RUC) like UPPER('%'+@DNI+'%') OR UPPER(AP_Paterno) like UPPER('%'+@Apellido+'%')
END
GO
/****** Object:  StoredProcedure [dbo].[pa_modificarCliente]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pa_modificarCliente]
@codigo Int
AS
BEGIN
SELECT * 
FROM Cliente
WHERE codigo_Cliente = @codigo
END
GO
/****** Object:  StoredProcedure [dbo].[EliminarCliente]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[EliminarCliente]
@DNI_RUC char(8)
AS
BEGIN
UPDATE Cliente SET Estado = 'F'
WHERE DNI_RUC = @DNI_RUC
END
GO
/****** Object:  StoredProcedure [dbo].[EliminarPersonal]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[EliminarPersonal]
@DNI char(8)
AS
BEGIN
UPDATE Personal SET Estado = 'F'
WHERE DNI = @DNI
END
GO
/****** Object:  StoredProcedure [dbo].[pa_buscarPersonal]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pa_buscarPersonal]
@DNI varchar(8),
@AP_Paterno varchar (20)
AS
BEGIN
SELECT *
FROM Personal
WHERE UPPER(DNI) like UPPER('%'+@DNI+'%') OR UPPER(AP_Paterno) like UPPER('%'+@AP_Paterno+'%')
END
GO
/****** Object:  Table [dbo].[Producto]    Script Date: 05/15/2011 23:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Producto](
	[Id_Producto] [int] IDENTITY(1,1) NOT NULL,
	[Cod_Producto] [varchar](15) NOT NULL,
	[Categoria] [varchar](20) NOT NULL,
	[Nombre] [varchar](20) NOT NULL,
	[Precio] [decimal](5, 2) NOT NULL,
	[Stock] [float] NOT NULL,
	[id_Proveedor] [int] NOT NULL,
	[Estado] [varchar](1) NOT NULL,
 CONSTRAINT [PK_Producto] PRIMARY KEY CLUSTERED 
(
	[Id_Producto] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[GetAllFromCliente]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetAllFromCliente]
AS
BEGIN
SELECT *
FROM Cliente
WHERE Estado = 'V'
END
GO
/****** Object:  StoredProcedure [dbo].[GetAllFromProveedor]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetAllFromProveedor]
AS
BEGIN
SELECT *
FROM Proveedor
WHERE Estado = 'V'
END
GO
/****** Object:  StoredProcedure [dbo].[RegistarProveedor]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[RegistarProveedor]
@codigoProveedor int out,
@razon_social varchar(20),
@telefono varchar(9),
@descripcion varchar(40),
@RUC varchar(11),
@Estado char(1) = 'V'
as
begin
	declare 
	@cont int
	
	select @cont = COUNT(*) from Proveedor where Codigo_Proveedor = @codigoProveedor
	
	if @cont =0
		begin
			insert into Proveedor(Razon_Social,Telefono,Descripcion,RUC,Estado)
			             values(@razon_social,@telefono,@descripcion,@RUC,@Estado)
			             
			set @cont = SCOPE_IDENTITY()
		end
		
	if @cont = 1
		begin
			update Proveedor 
				set Razon_Social = @razon_social,
				telefono = @telefono,
				Descripcion = @descripcion,				
				RUC = @RUC,
				Estado = @Estado
			where Codigo_Proveedor = @codigoProveedor		
			
		end
end
GO
/****** Object:  StoredProcedure [dbo].[GetAllFromProducto]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetAllFromProducto]
AS
BEGIN
SELECT *
FROM Producto
WHERE Estado = 'V'
END
GO
/****** Object:  StoredProcedure [dbo].[RegistarProducto]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[RegistarProducto]
@Id_Producto int out,
@Cod_Producto varchar(15),
@Categoria varchar(20),
@Nombre varchar(20),
@Precio decimal(5,2),
@Stock float,
@id_Proveedor int,
@Estado varchar(1) = 'V'
as
begin
	declare 
	@cont int
	
	select @cont = COUNT(*) from Producto where Id_Producto = @Id_Producto
	
	if @cont =0
		begin
			insert into Producto(Cod_Producto,Categoria,Nombre,Precio,Stock,id_Proveedor,Estado)
			             values(@Cod_Producto,@Categoria,@Nombre,@Precio,@Stock,@id_Proveedor,@Estado)
			             
			set @cont = SCOPE_IDENTITY()
		end
		
	if @cont = 1
		begin
			update Producto
				set Categoria = @Categoria,
				Nombre = @Nombre,
				Precio = @Precio,
				Stock = @Stock,
				id_Proveedor = @id_Proveedor,
				Cod_Producto = @Cod_Producto,
				Estado = @Estado
			where Id_Producto = @Id_Producto
			
		end
end
GO
/****** Object:  StoredProcedure [dbo].[GetFromProductoByNombre]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetFromProductoByNombre]
@Nombre varchar(20)
AS
BEGIN
SELECT *
FROM Producto
WHERE upper(Nombre) LIKE upper('%'+@Nombre+'%')
END
GO
/****** Object:  StoredProcedure [dbo].[EliminarProducto]    Script Date: 05/15/2011 23:43:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[EliminarProducto]
@Cod_Producto varchar(15)
AS
BEGIN
UPDATE Producto SET Estado = 'F'
WHERE Cod_Producto = @Cod_Producto
END
GO
/****** Object:  ForeignKey [FK_Producto_Proveedor]    Script Date: 05/15/2011 23:42:34 ******/
ALTER TABLE [dbo].[Producto]  WITH CHECK ADD  CONSTRAINT [FK_Producto_Proveedor] FOREIGN KEY([id_Proveedor])
REFERENCES [dbo].[Proveedor] ([Codigo_Proveedor])
GO
ALTER TABLE [dbo].[Producto] CHECK CONSTRAINT [FK_Producto_Proveedor]
GO
